﻿using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using NyMPH.Patches;
using NyMPH.Persistence.DSNyMPHTableAdapters;
using Logging;
using System.Diagnostics;
using System.Linq;

namespace NyMPH.Persistence
{
    /// <summary>
    /// Provides database support for the Keyword class to SQL-based database (currently SQL CE 3.5)
    /// </summary>
    public class KeywordDataHelper
    {
        private SqlCeConnection _conn;

        private tbKeywordTableAdapter _keywordTA;

        //Create a file log for errors just for debugging, otherwise write to event log
#if DEBUG
        private static EventLogHelper _elh = new EventLogHelper("NyMPH.Persistence.Database.Errors.log");

#else
        private EventLogHelper _elh = new EventLogHelper("NyMPH", "Database");
         
#endif

        #region Constructors
        /// <summary>
        /// Creates an instance of KeywordDataHelper. This requires a valid Connection object.
        /// </summary>
        /// <param name="Connection">Any connection object that inherits from DbConnection</param>
        /// <exception cref="NullReferenceException">An exception will be thrown if the DbConnection object is null.</exception>
        public KeywordDataHelper(SqlCeConnection Connection)
        {
            if (Connection != null)
            {
                //set data objects
                _conn = Connection;
                //TODO: Needed?
            }
            else
            {
                throw new NullReferenceException("Connection object cannot be null.");
            }
        }

        /// <summary>
        /// Creates an instance of KeywordDataHelper. No connection object is specified.
        /// </summary>
        public KeywordDataHelper()
        {

        }

        /// <summary>
        /// Accepts a valid tbKeywordTableAdapter object to work with data.
        /// </summary>
        /// <param name="KeywordTA"></param>
        public KeywordDataHelper(tbKeywordTableAdapter KeywordTA)
        {
            if (KeywordTA != null)
            {
                this._keywordTA = KeywordTA;

                //use connection object from table adapter until code is cleaned up
                this._conn = this._keywordTA.Connection;
            }
            else
            {
                throw new NullReferenceException("Table Adapter object cannot be null.");
            }

        }

        #endregion

        #region Persistence Methods

        /// <summary>
        /// Returns a list of Keyword instances belonging to a certain type.
        /// </summary>
        /// <param name="KeywordType">The type of Keyword to return.</param>
        /// <returns>A list of Keyword objects; this will be an empty list if an exception occurs.</returns>
        public List<Keyword> GetKeywordsByType(SDKCategories SDKType)
        {
            //create typed data objects
            DSNyMPH.tbKeywordDataTable _dtKeyword = new DSNyMPH.tbKeywordDataTable();

            try
            {
                //using (tbKeywordTableAdapter _taKeyword = new tbKeywordTableAdapter())
                //{
                //_taKeyword.Connection = _conn;
                ////fill typed table
                //_taKeyword.FillByType(_dtKeyword, SDKType.ToString());

                //use global table adapter to fill datatable
                _keywordTA.FillByType(_dtKeyword, SDKType.ToString());

                Console.WriteLine(_dtKeyword.Rows.Count);

                // }
            }
            catch (Exception ex)
            {
                _elh.WriteEvent(string.Format("Exception occurred while getting List of Keywords with KeywordType =  {0}. Exception details: {1}", SDKType.ToString(), ex.Message), EventLogEntryType.Error);
            }

            //create typed list with correct capacity
            List<Keyword> _keywords = new List<Keyword>(_dtKeyword.Rows.Count);

            foreach (DSNyMPH.tbKeywordRow _row in _dtKeyword.Rows)
            {
                //map datarow to Keyword object
                _keywords.Add(MapRowToKeyword(_row));
            }

            return _keywords;
        }

        //TODO: total kludge
        public int FindKeywordOID(Keyword _Keyword)
        {
            string _sql = "SELECT KeywordOID FROM tbKeyword " +
                          "WHERE KeywordName = @Name AND IsCustom = @IsCustom AND KeywordType = @Type";

            int _oid = -1;

            SqlCeCommand _cmd = new SqlCeCommand(_sql, _conn);
            _cmd.Parameters.AddWithValue("@Name", _Keyword.Name);
            _cmd.Parameters.AddWithValue("@IsCustom", _Keyword.IsCustom);
            _cmd.Parameters.AddWithValue("@Type", _Keyword.KeywordType.ToString());

            try
            {
                _cmd.Connection.Open();

                _oid = (int)_cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new NotImplementedException("FindKeywordOID threw an exception.", ex);
            }

            finally
            {
                _cmd.Connection.Close();
                _cmd.Dispose();
            }
            return _oid;
        }



        /// <summary>
        /// Persists an instance of Keyword to the database.
        /// </summary>
        /// <param name="Item">A Keyword instance to add or update. If added, this Keyword will have its OID property assigned.</param>
        /// <returns>The number of rows affected; this will be -1 if an exception occurs.</returns>
        public int SaveKeyword(ref Keyword Item)
        {
            int _result = 0;

            if (Item != null)
            {
                //create typed data objects
                DSNyMPH.tbKeywordDataTable _dtKeyword = new DSNyMPH.tbKeywordDataTable();

                try
                {
                    using (tbKeywordTableAdapter _taKeyword = new tbKeywordTableAdapter())
                    {
                        //fill typed table with instance(s) of this Keyword
                        _taKeyword.FillByOID(_dtKeyword, Item.OID);

                        //If no records are returned, add this Keyword to the datatable.
                        if (_dtKeyword.Count == 0)
                        {
                            //add new row                
                            //_result = _taKeyword.Insert(Item.Name, Item.IsCustom, Item.KeywordType.ToString());


                            DSNyMPH.tbKeywordRow _row = _dtKeyword.NewtbKeywordRow();

                            //TODO: review this process
                            //set properties
                            _row.KeywordName = Item.Name;
                            _row.KeywordType = Item.KeywordType.ToString();
                            _row.IsCustom = Item.IsCustom;

                            _dtKeyword.AddtbKeywordRow(_row);
                            //update changes to datatable using table adapter
                            _result = _taKeyword.Update(_dtKeyword);

                            Item.OID = FindKeywordOID(Item);

                        }
                        else
                        {
                            //should only be one instance
                            //TODO: there's probably a better way to do this...
                            DSNyMPH.tbKeywordRow _row = (DSNyMPH.tbKeywordRow)_dtKeyword.Rows[0];

                            //TODO: review this process
                            //set properties
                            _row.KeywordName = Item.Name;
                            _row.KeywordType = Item.KeywordType.ToString();
                            _row.IsCustom = Item.IsCustom;

                            //update changes to datatable using table adapter
                            _result = _taKeyword.Update(_dtKeyword);
                        }
                    }
                }
                catch (Exception ex)
                {
                    //write to logging
                    _elh.WriteEvent(string.Format("Exception occurred while saving Keyword   {0}. Exception details: {1}", Item.ToString(), ex.Message), EventLogEntryType.Error);

                    _result = -1;
                }
            }
            else
            {
                _result = -1;
            }
            return _result;
        }

        /// <summary>
        /// Deletes the specified Keyword from the database. Only custom Keywords can be deleted.
        /// </summary>
        /// <param name="Item">The Keyword to delete.</param>
        /// <returns>The number of records affected. This will be -1 if an exception occurs.</returns>
        public int DeleteKeyword(Keyword Item)
        {
            int _result = 0;

            //verify not null, isn't 'built-in' Keyword, and OID isn't null
            if ((Item != null) && (Item.IsCustom) && (Item.OID != -1))
            {
                //create typed data objects
                DSNyMPH.tbKeywordDataTable _dtKeyword = new DSNyMPH.tbKeywordDataTable();

                try
                {
                    using (tbKeywordTableAdapter _taKeyword = new tbKeywordTableAdapter())
                    {
                        //fill typed table with instance(s) of this Keyword
                        _taKeyword.FillByOID(_dtKeyword, Item.OID);

                        //If no records are returned, add this Keyword to the datatable.
                        if (_dtKeyword.Count == 1)
                        {
                            //should only be one instance
                            _dtKeyword.Rows[0].Delete();
                        }
                        else
                        {
                            //TODO: Shouldn't be multiple rows, but handle it better
                            throw new NotImplementedException("Handle multiple Keywords");
                        }
                        // update deletion changes to datatable using table adapter
                        _result = _taKeyword.Update(_dtKeyword);
                    }
                }
                catch (Exception ex)
                {
                    //write to logging
                    _elh.WriteEvent(string.Format("Exception occurred while deleting Keyword   {0}. Exception details: {1}", Item.ToString(), ex.Message), EventLogEntryType.Error);

                    _result = -1;
                }
            }
            else
            {
                _result = -1;
            }

            return _result;

        }

        //todo: return new Keyword?
        public Keyword LoadKeyword(int KeywordOID)
        {
            //create typed data objects
            DSNyMPH.tbKeywordDataTable _dtKeyword = new DSNyMPH.tbKeywordDataTable();

            try
            {
                using (tbKeywordTableAdapter _taKeyword = new tbKeywordTableAdapter())
                {
                    //fill typed table
                    _taKeyword.FillByOID(_dtKeyword, KeywordOID);
                }
            }
            catch (Exception ex)
            {
                _elh.WriteEvent(string.Format("Exception occurred while getting Keywords with KeywordOID =  {0}. Exception details: {1}", KeywordOID.ToString(), ex.Message), EventLogEntryType.Error);
            }

            if (_dtKeyword.Rows.Count == 0)
            {
                return new Keyword();
            }
            else
            {
                //cast typed table to keyword object
                return MapRowToKeyword(_dtKeyword.Rows[0]);
            }
        }

        /// <summary>
        /// Converts an instance of tbKeywordRow to a 'real' Keyword object.
        /// </summary>
        /// <param name="KeywordRow">A single instance of tbKeywordRow to parse into a Keyword object.</param>
        /// <returns>A new Keyword object with properties set to the values in the row.</returns>
        public static Keyword MapRowToKeyword(DSNyMPH.tbKeywordRow KeywordRow)
        {
            Keyword _new = new Keyword();

            //check for null row, then map fields to object
            if (KeywordRow != null)
            {
                _new.OID = KeywordRow.KeywordOID;
                _new.Name = KeywordRow.KeywordName;
                _new.IsCustom = KeywordRow.IsCustom;
                _new.KeywordType = ParseSDK(KeywordRow.KeywordType);
            }

            return _new;
        }

        public static Keyword MapRowToKeyword(System.Data.DataRow KeywordRow)
        {
            Keyword _new = new Keyword();

            //check for null row, then map fields to object
            if (KeywordRow != null)
            {

                _new.OID = (int)KeywordRow["KeywordOID"];
                _new.Name = (string)KeywordRow["KeywordName"];
                _new.IsCustom = (bool)KeywordRow["IsCustom"];
                _new.KeywordType = ParseSDK((string)KeywordRow["KeywordType"]);
            }

            return _new;
        }

        /// <summary>
        /// Parses a string value into a Sound Description Keyword Category (SDKCategories enumeration type). No exceptions will be thrown.
        /// </summary>
        /// <param name="SDKString">The string to parse into a SDKCategories value.</param>
        /// <returns>This will return a SDKCategories value. If the string is not valid, it will return Unknown</returns>

        public static SDKCategories ParseSDK(string SDKString)
        {
            SDKCategories SDKEnum = SDKCategories.Unknown;

            //make sure string isn't null
            if (!String.IsNullOrEmpty(SDKString))
            {
                //parse string into SDKCategory and ignore case
                //kludge until database storage of this concept is resolved
                switch (SDKString.ToLowerInvariant())
                {
                    case "source":
                    case "soundsource":
                    case "1":
                        SDKEnum = SDKCategories.SoundSource;
                        break;

                    case "instrument":
                    case "instrumenttype":
                    case "2":
                        SDKEnum = SDKCategories.InstrumentType;
                        break;

                    case "timbre":
                    case "timbre_characteristic":
                    case "3":
                        SDKEnum = SDKCategories.Timbre_Characteristic;
                        break;

                    case "articulation":
                    case "4":
                        SDKEnum = SDKCategories.Articulation;
                        break;

                    default:
                        SDKEnum = SDKCategories.Unknown;
                        break;
                }
            }

            return SDKEnum;
        }//end ParseSDK

        // LINQ-to-SQL versions

        ///// <summary>
        ///// Creates an instance of KeywordFactory. This requires a valid DataContext object.
        ///// </summary>
        ///// <param name="dataContext">An instance of NyMPHDataContext to use for LINQ.</param>

        //public KeywordFactory(NyMPHDataContext dataContext)
        //{
        //    this._dc = dataContext;
        //}

        ///// <summary>
        ///// Returns a list of Keyword instances belonging to a certain type.
        ///// </summary>
        ///// <param name="KeywordType">The type of Keyword to return.</param>
        ///// <returns>A list of Keyword objects</returns>
        //public List<Keyword> GetKeywordsByType(SDKCategories SDKType)
        //{
        //    //Return list of Keywords that match the type 
        //    return (from k in _dc.Keywords
        //            where k.KeywordType == SDKType
        //            select k).ToList<Keyword>();
        //}


        //public bool SaveKeyword(Keyword Item)
        //{
        //    //make temporary data context
        //    NyMPHDataContext _tempDC = new NyMPHDataContext(_dc.Connection);

        //    //get a 'copy' of the matching keyword to update
        //    Keyword _original = (from k in _tempDC.Keywords
        //               where k.OID == Item.OID
        //               select k).SingleOrDefault<Keyword>();

        //    //If Item doesn't already exist, Insert it
        //    if (_original == null)
        //    {
        //        //TODO: not sure if this works the way I think it does...
        //        _tempDC.Keywords.Attach(Item, _original);

        //    }
        //    else
        //    {
        //        //don't bother updating if they are equal
        //        if (_original != Item)
        //        {
        //            //TODO: not sure if this works
        //            //Set original object to the new Item object and commit changes.
        //            _original = Item;
        //            _tempDC.SubmitChanges();
        //        }
        //    }
        //    _tempDC.Dispose();
        //}

        #endregion
    }
}
